﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Xml.Linq;
using System.Collections.Generic;
using System.Data.SqlClient;
using Utils;


/// <summary>
/// Summary description for MemberDAO
/// </summary>
public class MemberDAO : SQlHelper
{
    public DataSet ValidateUser(string username, string password)
    {
        return Utils.DatabaseUtils.SqlHelper.ExecuteDataset(Common.Conn, "Members_CheckUserName", username, password);
        /*using (SqlConnection conn = GetConnectionString())
        {           
            bool isValid = false;
            SqlCommand cmd = new SqlCommand("Members_CheckUserName", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = username;
            cmd.Parameters.Add("@Password", SqlDbType.VarChar).Value = password;            
            SqlDataReader reader = cmd.ex();
            if (reader.HasRows)
            {
                isValid = true;
                reader.Close();
                conn.Close();
            }
            return isValid;
        }*/
    }

    public bool CheckUniqueCode(string refCode)
    {
        using (SqlConnection conn = GetConnectionString())
        {
            bool isValid = false;
            SqlCommand sqlCmd = new SqlCommand("select * from Members where uniquecode like '%" + refCode + "%'", conn);
            sqlCmd.CommandType = CommandType.Text;
            SqlDataReader dr = sqlCmd.ExecuteReader();
            if (dr.HasRows)
            {
                isValid = true;
                dr.Close();
                conn.Close();
            }
            return isValid;
        }
    }


    public List<MembersUI> GetMember()
    {
        using (SqlConnection conn = GetConnectionString())
        {
            List<MembersUI> list = new List<MembersUI>();
            SqlCommand sqlCmd = new SqlCommand("SELECT * From Members", conn);
            sqlCmd.CommandType = CommandType.Text;
            SqlDataReader reader = sqlCmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    list.Add(IDataReaderMember(reader));
                }
                reader.Close();
                conn.Close();
            }
            return list;
        }
    }

    public bool Changepassword(string username, string newPass)
    {
        using (SqlConnection conn = GetConnectionString())
        {
            SqlCommand sqlCmd = new SqlCommand("Member_ChangePassword", conn);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.Add("@Username", SqlDbType.VarChar).Value = username;
            sqlCmd.Parameters.Add("@NewPassword", SqlDbType.VarChar).Value = newPass;            
            sqlCmd.ExecuteNonQuery();
            conn.Close();
            return true;
        }
    }

    ////Insert default point to members
    //public bool InsertPoint(int memberID, decimal pointNumber)
    //{
    //    using (SqlConnection conn = GetConnectionString())
    //    {
    //        SqlCommand sqlComm = new SqlCommand("Member_insertPoint", conn);
    //        sqlComm.CommandType = CommandType.StoredProcedure;
    //        sqlComm.Parameters.Add("@MemberID", SqlDbType.Int).Value = memberID;
    //        sqlComm.Parameters.Add("@PointNumber", SqlDbType.Decimal).Value = pointNumber;
    //        sqlComm.ExecuteNonQuery();
    //        conn.Close();
    //        return true;
    //    }
    //}

    public int InsertNewMember(MembersUI member)
    {
        using (SqlConnection conn = GetConnectionString())
        {
            SqlCommand cmd = new SqlCommand("Members_Add2", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            //chu y khi insert code nguwoi gioi thiẻu
            cmd.Parameters.Add("@UniqueCode", SqlDbType.VarChar).Value = member.UniqueCode;
            cmd.Parameters.Add("@LevelInfo", SqlDbType.Int).Value = member.Level;
            cmd.Parameters.Add("@Username", SqlDbType.NVarChar).Value = member.UserName;
            cmd.Parameters.Add("@HashedPassword", SqlDbType.NVarChar).Value = member.Password;
            cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = member.Email;
            cmd.Parameters.Add("@FullName", SqlDbType.NVarChar).Value = member.FullName;
            cmd.Parameters.Add("@Birthday", SqlDbType.VarChar).Value = member.Birthday;
            cmd.Parameters.Add("@Mobile", SqlDbType.NVarChar).Value = member.Mobile;
            cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value = member.Address;
            int uID = cmd.ExecuteNonQuery();
            conn.Close();
            return uID;
        }
    }

    public DataSet GetByID(int memberId)
    {
        return Utils.DatabaseUtils.SqlHelper.ExecuteDataset(Common.Conn, "Members_Get", memberId);
    }

    public int UpdateMember(MembersUI member)
    {
        using (SqlConnection conn = GetConnectionString())
        { 
            SqlCommand cmd = new SqlCommand("Members_Update", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            //chu y khi insert code nguwoi gioi thiẻu
            cmd.Parameters.Add("@MemberID", SqlDbType.Int).Value = member.MemberID;
            cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = member.Email;
            cmd.Parameters.Add("@FullName", SqlDbType.NVarChar).Value = member.FullName;
            cmd.Parameters.Add("@Birthday", SqlDbType.VarChar).Value = member.Birthday;
            cmd.Parameters.Add("@Mobile", SqlDbType.NVarChar).Value = member.Mobile;
            cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value = member.Address;
            int uID = cmd.ExecuteNonQuery();
            conn.Close();
            return uID;
        }
    }


    public bool CheckUser(string username) 
    { 
        using(SqlConnection conn = GetConnectionString())
        {
            bool isValid = false;
            SqlCommand sqlCmd = new SqlCommand("Member_CheckUser", conn);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.Add("@Username", SqlDbType.NVarChar).Value = username;
            SqlDataReader dr = sqlCmd.ExecuteReader();
            if (dr.HasRows)
            {
                isValid = true;
                dr.Close();
                conn.Close();
            }
            return isValid;
        }
    }   

    private MembersUI IDataReaderMember(IDataReader iReader)
    {
        MembersUI member = new MembersUI();
        member.MemberID = (iReader["MemberID"] is DBNull) ? int.MinValue : (int) iReader["MemberID"];
        member.FullName = (iReader["UniqueCode"] is DBNull) ? String.Empty : (string)iReader["UniqueCode"];
        member.Level = (iReader["Level"] is DBNull) ? int.MinValue : (int)iReader["MemberID"];
        member.UserName = (iReader["Username"] is DBNull) ? String.Empty : (string)iReader["Username"];
        member.Password = (iReader["Password"] is DBNull) ? String.Empty : (string)iReader["Password"];
        member.Email = (iReader["Email"] is DBNull) ? String.Empty : (string)iReader["Email"];
        member.FullName = (iReader["FullName"] is DBNull) ? String.Empty : (string)iReader["FullName"];
        member.Birthday = (iReader["Birthday"] is DBNull) ? String.Empty : (string)iReader["Birthday"];
        member.Mobile = (iReader["Mobile"] is DBNull) ? String.Empty : (string)iReader["Mobile"];
        member.Address = (iReader["Address"] is DBNull) ? String.Empty : (string)iReader["Address"];

        return member;
    }



}
